×
☰ See All Chapters

Converting character to numeric in Oracle

Numeric values are right-justified in the output results, whereas character string data types are left-justified. We can convert only Numeric String to Numeric.

Examples to convert character to string in oracle

Creating table for demonstrating conversion from character to string

CREATE TABLE CONVERSION_FUNCTIONS

(

  NUMERIC_VALUE  INTEGER,

  STRING         VARCHAR(10 ),

  DATE_VALUE     DATE

);

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES (222, 'BBB', to_date('1990-05-06','yyyy-mm-dd'));

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES (333, 'CCC', to_date('1999-06-07','yyyy-mm-dd'));

INSERT INTO CONVERSION_FUNCTIONS (NUMERIC_VALUE, STRING, DATE_VALUE) VALUES (444, 'DDD', to_date('1995-12-18','yyyy-mm-dd'));

Example 1

SELECT

   NUMERIC_VAL,

   NUMERIC_STRING_VAL,

TO_CHAR (NUMERIC_VAL),

TO_NUMBER (NUMERIC_STRING_VAL),

TO_DATE

   (

      NUMERIC_STRING_VAL,

      'MMDDYYYY'

   )

FROM

   CONVERSION_FUNCTIONS

oracle-converting-character-to-numeric-0

Example 2

SELECT

   NUMERIC_VAL,

TO_CHAR ('123')

FROM

   CONVERSION_FUNCTINS

oracle-converting-character-to-numeric-1
 

Example 3

SELECT TO_DATE

   (

      NUMERIC_STRING_VAL,

      'MM/DD/YYYY'

   )

FROM

   CONVERSION_FUNCTIONS

oracle-converting-character-to-numeric-2
 

Example 4

SELECT TO_CHAR (DATE_VAL)

FROM

   CONVERSION_FUNCTIONS

oracle-converting-character-to-numeric-3
 
All Chapters
Author